﻿#region Creditos

/* Autor: Edilberto Sánchez Forero	*/
/* Email: esanchezfo@gmail.com		*/
/* Licencia: LGPL, Software Libre	*/
/* País: Colombia, Bogotá			*/

#endregion

#region Librerias

using System;
using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Data;

using ESF.General;
using ESF.Object.XmlRepresenter.Rules;
using ESF.Object.XmlRepresenter;
using ESF.General.Text;

#endregion


namespace ESF.Object.DataBaseGenerator
{	
	// TODO: Soportar particiones
	// TODO: Soportar tablespaces (grupos de archivos)
	// TODO: Soportar dimensionamiento para generar particiones y tablespaces dinamicamente
	public abstract class DbGenerator
	{
		#region Enumeraciones
		
		// TODO: Soportar identificar los campos no incluidos en las tablas, para generar los respectivos ALTER	
		public enum GeneratorTypes : byte
		{
			DeleteAll,
			CreateAll,
			DeleteAllCreateAll
		}
			
		#endregion
	
	
		#region Miembros
		
		private string _DataBaseName;
		
		private string _ConnectionStringName;
		
		private List<XmlObject> _Objects;
		
		private List<string> _SetNames;
		
		protected TextWriter _Writer;
		
		private DbProviders _DbProvider;
		
		private XmlObjectSetManager _ObjectSetManager;
		
		#region Comodines
		
		private string _NamesSeparator;
		
		private char _CommentsSeparator;
		
		private string _ParamsSeparator; 
		
		private string _VarsSeparator;
		
		#endregion
				
		#endregion
			
		
		#region Propiedades
				
		public string DataBaseName { get { return this._DataBaseName; } }
		
		public string ConnectionStringName { get { return this._ConnectionStringName; } }
		
		public XmlObjectSetManager ObjectSetManager { get { return this._ObjectSetManager; } }
		
		
		#region Comodines
		
		public string NamesSeparator { get { return this._NamesSeparator; } }
		
		public char CommentsSeparator { get { return this._CommentsSeparator; } }
		
		public string ParamsSeparator { get { return this._ParamsSeparator; } }
		
		public string VarsSeparator { get { return this._VarsSeparator; } }
		
		#endregion
		
		#endregion
		
		
		#region Constructor
		
		protected DbGenerator () {}
		
		
		public DbGenerator 
		(
			DbProviders pobjDbProvider, string pstrDataBaseName, 
			string pstrConnectionStringName,
			XmlObjectSetManager pobjObjectSetManager,
			string pstrNamesSeparator, char pchrCommentsSeparator, 
			string pstrParamsSeparator, string pstrVarsSeparator
		)
		{
			Validate objVal;
			
			
			objVal = new Validate ();
			
			
			this._DbProvider = pobjDbProvider;
		
			this._DataBaseName = pstrDataBaseName;
			
			this._NamesSeparator = pstrNamesSeparator;
			
			this._CommentsSeparator = pchrCommentsSeparator;
			
			this._ParamsSeparator = pstrParamsSeparator;
			
			this._VarsSeparator = pstrVarsSeparator;
			
			this._ObjectSetManager = pobjObjectSetManager;
			
			
			
			this._SetNames = new List<string> ();
			
			this._Objects = new List<XmlObject> ();
			
			
			this.SetSetNames (this._ObjectSetManager.SetNames);
			
			this.SetObjects (this._ObjectSetManager.SortedObjects);
			
			
			objVal.ObjectName = "Generador de Base de Datos";
		
			objVal.Filled (true, "Nombre de la Cadena de Conexión", pstrConnectionStringName);
			
			this._ConnectionStringName = pstrConnectionStringName;
		}
		
		#endregion
		
		
		#region Funciones de Generacion General
		
		public string GenerateDbScripts (bool pblnIncludeDB, bool pblnIncludeTables, bool pblnIncludeSPs, GeneratorTypes pobjGeneratorType)
		{
			StringBuilder stbScripts;
			
			
			stbScripts = new StringBuilder ();
			
			this._Writer = new StringWriter (stbScripts);
			
			this.CallGenerateDbScripts (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs, pobjGeneratorType);

			return stbScripts.ToString ();
		}
		
		
		public void GenerateDbScripts (string pstrFilePath, bool pblnIncludeDB, bool pblnIncludeTables, bool pblnIncludeSPs, GeneratorTypes pobjGeneratorType)
		{
			try
			{
				this._Writer = FileHelper.CreateText (pstrFilePath);
				
				this.CallGenerateDbScripts (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs, pobjGeneratorType);
				
				this._Writer.Close ();
			}
			catch (Exception excEx)
			{
				throw new GenericException (GenericException.CRI_FILE_ERROR, string.Format ("No pudo generarse el archivo en {0}", pstrFilePath), excEx);
			}
		} 
		
		
		private void CallGenerateDbScripts (bool pblnIncludeDB, bool pblnIncludeTables, bool pblnIncludeSPs, GeneratorTypes pobjGeneratorType)
		{
			if (this._SetNames.Count == 0 || this._Objects.Count == 0)
			{
				throw new GenericException (GenericException.CRI_INVALID_PARAMETER, "No han sido definidos los nombres de los conjuntos");
			}
			
			if (this._Objects.Count == 0)
			{
				throw new GenericException (GenericException.CRI_INVALID_PARAMETER, "No han sido definidos los objetos organizados");
			}
		
			this.AddComment (string.Format ("Fecha de Generación: {0}\nTipo de Generación: {1}", DateTime.Now, pobjGeneratorType));
			
			switch (pobjGeneratorType)
			{
				case GeneratorTypes.DeleteAll:
					this.GenerateDeleteAll (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs);
					break;
					
				case GeneratorTypes.CreateAll:
					this.GenerateCreateAll (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs);
					break;
					
				case GeneratorTypes.DeleteAllCreateAll:					
					this.GenerateDeleteAll (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs);
					this.GenerateCreateAll (pblnIncludeDB, pblnIncludeTables, pblnIncludeSPs);
					break;
			}
		}
		
		#endregion
		
		
		#region Funciones
		
		public static string GenerateSPName (string pstrNamesSeparator, string pstrDataBaseName, XmlObject pobjObject, string pstrOperationName)
		{
			return string.Format ("{0}{1}{0}.{0}{2}{0}", 
				pstrNamesSeparator, pstrDataBaseName, 
				DbGenerator.GenerateSimpleSPName (pobjObject, pstrOperationName));
		}
		
		
		public static string GenerateSimpleSPName (XmlObject pobjObject, string pstrOperationName)
		{
			return string.Format ("CSP_{0}_{1}_{2}", pobjObject.SetName, pobjObject.Alias, pstrOperationName);
		}
		
		
		public string GenerateSPName (XmlObject pobjObject, string pstrOperationName)
		{
			return DbGenerator.GenerateSPName (this.NamesSeparator, this.DataBaseName, pobjObject, pstrOperationName);
		}
		
		
		public void SetSetNames (params string [] pstrSetNames)
		{
			this._SetNames.Clear ();
			this._SetNames.AddRange (pstrSetNames);
		}
		
		
		public void ClearSetNamesObjects ()
		{
			this._SetNames.Clear ();
			this._Objects.Clear ();
		}
		
		
		public void SetObjects (List<XmlObject> pobjObjects)
		{
			this._Objects.Clear ();
			this._Objects = pobjObjects;
		}
		
		
		private void AddComment (string pstrComment)
		{
			StringBuilder stbComments;
			
			
			stbComments = new StringBuilder ();
			
			this.AddComment (ref stbComments, pstrComment);
			
			this._Writer.Write (stbComments.ToString ());
			this._Writer.Flush ();
		}
		
		
		protected void AddComment (ref StringBuilder pstbSql, string pstrComment)
		{
			pstbSql.AppendLine (new String (this._CommentsSeparator, 2));
			
			foreach (string strComment in pstrComment.Split ('\n'))
			{
				pstbSql.AppendFormat ("{0}{0} {1}\n", this._CommentsSeparator, strComment);
			}			
			
			pstbSql.AppendLine (new String (this._CommentsSeparator, 2));
		}
		
		
		protected string GenerateListColumns (XmlObject pobjObject)
		{
			bool blnAddedFirst;
			
			StringBuilder stbResult;
			
			XmlObject objRef;
			
			
			blnAddedFirst = false;
			
			stbResult = new StringBuilder ();
			
			
			foreach (XmlMember objMember in pobjObject.Members)
			{
				if (objMember.Type == ValueTypes.Custom)
				{
					objRef = this._ObjectSetManager.GetObjectBy (objMember);
				
					if (!objRef.IsArray)
					{
						stbResult.Append (TextHelper.NL_2_TAB);
						
						if (blnAddedFirst)
						{
							stbResult.Append (",");
						}
						
						stbResult.AppendFormat ("ID{0} AS {1}_ID{2}", objMember.Name, pobjObject.Alias, objMember.Alias);						
						blnAddedFirst = true;
					}
				}
				else
				{
					stbResult.Append (TextHelper.NL_2_TAB);
					
					if (blnAddedFirst)
					{
						stbResult.Append (",");
					}
				
					stbResult.AppendFormat ("{0} AS {1}_{2}", objMember.Name, pobjObject.Alias, objMember.Alias);
					blnAddedFirst = true;
				}
			}
			
			return stbResult.ToString ();
		}
			
			
		protected string GenerateSPParameterID(XmlObject pobjObject)
		{
			XmlObject objExtRef;
		
		
			if (pobjObject.IsArray)
			{
				objExtRef = this.ObjectSetManager [pobjObject.SetName] [pobjObject.Type];
				
				return this.GenerateSPParameterID (objExtRef);
			}
			else
			{
				return this.GenerateSPParameterID ("ID" + pobjObject.Name, pobjObject.IDType, pobjObject.IDMaxLength);
			}
		}
				
		#endregion
		
		
		#region Funciones de Eliminacion
			
		private void GenerateDeleteAll (bool pblnIncludeDB, bool pblnIncludeTables, bool pblnIncludeSPs)
		{		
			if (pblnIncludeDB) 
			{
				this._Writer.WriteLine ();
				this.AddComment ("Eliminación de la Base de Datos");				
				this.AddDeleteDataBase ();
				this._Writer.WriteLine ();
				
				this._Writer.Flush ();
			}
			
			if (pblnIncludeTables) 
			{
				this.AddDeleteTables ();
			}
			
			if (pblnIncludeSPs)
			{
				this.AddDeleteSPs ();
			}
		}
		
		
		private void AddDeleteTables ()
		{
			XmlObject objRefObject;
		
			int intI;
			
				
			this._Writer.WriteLine ();
			this.AddComment ("Eliminacion de las Tablas de Colecciones");
			
			// Se eliminan primero las colecciones
			foreach (XmlObject objObject in this._Objects.FindAll (o => o.IsArray))
			{
				// Se busca por aquellos objetos dependientes de estas colecciones
				foreach (XmlObject objObject_I in this._Objects.FindAll (o => !o.IsArray))
				{
					foreach (XmlMember objMember in objObject_I.Members.FindAll (m => m.CustomType == objObject.Name))
					{
						objRefObject = this.ObjectSetManager.GetObjectBy (objMember);
						
						this.AddDeleteTable (this.GenerateTableNameForRefArray (objObject_I, objMember, objRefObject));
						this._Writer.WriteLine ();
					}
				}
			}
		
			this._Writer.WriteLine ();
			this.AddComment ("Eliminacion de las Tablas Comunes");
		
			// Se eliminan los demas objetos		
			for (intI = this._Objects.Count - 1; intI >= 0; intI --)
			{
				if (!this._Objects [intI].IsArray)
				{
					this.AddDeleteTable (this._Objects [intI]);
					this._Writer.WriteLine ();
				}
			}
				
			this._Writer.Flush ();
		}
		
		
		private void AddDeleteSPs ()
		{
			XmlObject objRef;
			XmlObject objExtRef;
		
		
			this._Writer.WriteLine ();
			this.AddComment ("Eliminacion de los Procedimientos Almacenados");
			
			foreach (XmlObject objObject in this._Objects.FindAll (o => !o.IsArray))
			{
				this._Writer.WriteLine ();
				this.AddComment (string.Format ("Procedimientos almacenados para {0}.{1}", objObject.SetName, objObject.Name));
				
				//this.AddDeleteSPCount (objObject);
				this.AddDeleteSPInsertUpdate (objObject);				
				this.AddDeleteSPDeleteByID (objObject);
				this.AddDeleteSPSelect (objObject);
				//this.AddDeleteSPSelectByID (objObject);
				this.AddDeleteSPSelectList (objObject);
				this.AddDeleteSPSelectEnabledList (objObject);
				
				foreach (XmlMember objMember in objObject.CustomMembers)
				{
					objRef = this.ObjectSetManager.GetObjectBy (objMember);
					
					if (objRef.IsArray)
					{
						objExtRef = this.ObjectSetManager [objRef.SetName][objRef.Type];
					
						this.AddDeleteSPDeleteArrayByRef (objObject, objMember, objExtRef);
						this.AddDeleteSPDeleteArrayBySelf (objObject, objMember, objExtRef);
						this.AddDeleteSPSelectRefArrayByMe (objObject, objMember, objExtRef); 
						this.AddDeleteSPSelectArraySelfByRef (objObject, objMember, objExtRef);  
						this.AddDeleteSPInsertUpdateArray (objObject, objMember, objExtRef);
					}
				}
			}
			
			this._Writer.Flush ();
		}
		
		
		#region Llamados a Delete SPs
		
		//protected void AddDeleteSPCount (XmlObject pobjObject)
		//{
		//    this.AddDeleteSP (this.GenerateSPName (pobjObject, "Count"));
		//}
		
		protected void AddDeleteSPSelect(XmlObject pobjObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "Select"));
		}
		
		protected void AddDeleteSPSelectList(XmlObject pobjObject)
		{
			if (!pobjObject.HasNameMember) { return; }
			
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "SelectList"));
		}
		
		protected void AddDeleteSPSelectEnabledList(XmlObject pobjObject)
		{
			if (!pobjObject.HasEnabledMember) { return; }
			
			if (!pobjObject.HasNameMember) { return; }
			
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "SelectEnabledList"));
		}
		
		protected void AddDeleteSPDeleteByID(XmlObject pobjObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "DeleteByID")); 
		}

		protected void AddDeleteSPInsertUpdate(XmlObject pobjObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "InsertUpdate")); 
		}

		//protected void AddDeleteSPSelectByID (XmlObject pobjObject)
		//{
		//    if (pobjObject.IsArray) { return; }
			
		//    this.AddDeleteSP (this.GenerateSPName (pobjObject, "SelectByID"));
		//}

		protected void AddDeleteSPDeleteArrayByRef (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "Delete_ArrayOf" + pobjMember.Alias + "By" + pobjExtRefObject.Alias));
		}
		
		protected void AddDeleteSPDeleteArrayBySelf (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "Delete_ArrayOf" + pobjMember.Alias + "By" + pobjObject.Alias));
		}

		protected void AddDeleteSPInsertUpdateArray (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "Insert_ArrayOf" + pobjMember.Alias));
		}

		protected void AddDeleteSPSelectRefArrayByMe (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "SelectRefArrayOf_" + pobjMember.Alias + "_By_" + pobjObject.Alias));
		}

		protected void AddDeleteSPSelectArraySelfByRef (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject)
		{
			this.AddDeleteSP (this.GenerateSPName (pobjObject, "SelectSelfArrayOf_" + pobjObject.Alias + "_By_" + pobjMember.Alias));
		}
		
		#endregion
		
		#endregion
		
		
		#region Funciones de Creacion
		
		protected string GenerateColumnName (XmlMember pobjMember, bool pblnIncludeEqualsChar)
		{
			XmlObject objRefObject;
			
		
			if (pobjMember.Type == ValueTypes.Custom)
			{
				objRefObject = this.ObjectSetManager.GetObjectBy (pobjMember);
				
				if (objRefObject.IsArray)
				{
					return string.Empty;
				}
				
				return "ID" + pobjMember.Name + (pblnIncludeEqualsChar ? " = " : string.Empty);
			}
			
			return pobjMember.Name + (pblnIncludeEqualsChar ? " = " : string.Empty);
		}
		
		
		private void GenerateCreateAll (bool pblnIncludeDB, bool pblnIncludeTables, bool pblnIncludeSPs)
		{
			if (pblnIncludeDB) 
			{
				this._Writer.WriteLine ();
				this.AddComment ("Creación de la Base de Datos");
				this.AddCreateDataBase ();
				this._Writer.WriteLine ();
				
				this._Writer.Flush ();
			}
			
			if (pblnIncludeTables) 
			{
				this.AppendCreateTables ();
			}
			
			if (pblnIncludeSPs)
			{
				this.AddCreateSPs ();
			} 
		}
		
		
		private void AddCreateSPs ()
		{
			XmlObject objRef;
		
			XmlObject objExtRef;
			
		
			this._Writer.WriteLine ();
			this.AddComment ("Creación de los Procedimientos Almacenados");
			
			foreach (XmlObject objObject in this._Objects.FindAll (o => !o.IsArray))
			{
				this._Writer.WriteLine ();
				this.AddComment (string.Format ("Procedimientos almacenados para {0}.{1}", objObject.SetName, objObject.Name));
				
				//this.AddCreateSPCount (objObject);
				this.AddCreateSPInsertUpdate (objObject);
				this.AddCreateSPDeleteByID (objObject);
				this.AddCreateSPSelect (objObject);
				//this.AddCreateSPSelectByID (objObject);
				this.AddCreateSPSelectList (objObject);
				this.AddCreateSPSelectEnabledList (objObject);
				
				foreach (XmlMember objMember in objObject.CustomMembers)
				{
					objRef = this.ObjectSetManager.GetObjectBy (objMember);
					
					if (objRef.IsArray)
					{
						objExtRef = this.ObjectSetManager [objRef.SetName][objRef.Type];
					
						this.AddCreateSPDeleteArrayByRef (objObject, objMember, objExtRef);
						this.AddCreateSPDeleteArrayBySelf (objObject, objMember, objExtRef);
						this.AddCreateSPSelectRefArrayByMe (objObject, objMember, objExtRef); 
						this.AddCreateSPSelectArraySelfByRef (objObject, objMember, objExtRef); 
						this.AddCreateSPInsertArray (objObject, objMember, objExtRef);
					}
				}
			}
			
			this._Writer.Flush ();
		}

		
		private void AppendCreateTables ()
		{
			int intI;
		
			Dictionary<XmlObject, StringBuilder> dicExtraFKTables;
			
			XmlObject objArrayNotUsed;
				
			StringBuilder stbSql;
			StringBuilder stbExtraFKs;
			StringBuilder stbExtraCollectionTable;
								
			
			this._Writer.WriteLine ();
			this._Writer.WriteLine ();
			this.AddComment ("Creación de las Tablas");
						
			dicExtraFKTables = new Dictionary<XmlObject,StringBuilder> ();
			
			objArrayNotUsed = null;
				
			stbSql = new StringBuilder ();
			stbExtraFKs = new StringBuilder ();
			stbExtraCollectionTable = new StringBuilder ();
					
			foreach (XmlObject pobjObject in this._Objects)
			{
				if (pobjObject.IsArray)
				{
					if (dicExtraFKTables.ContainsKey (pobjObject))
					{
						stbSql.Append (dicExtraFKTables [pobjObject].ToString ());
						dicExtraFKTables.Remove (pobjObject);
					}
					else
					{
						objArrayNotUsed = pobjObject;
					}
				}
				else
				{
					#region Se generan las tablas para las colecciones
					
					foreach (XmlMember pobjMember in pobjObject.CustomMembers)
					{
						XmlObject objRefObject;
						XmlObject objExtRefObject;
					
						
						objRefObject = this.ObjectSetManager.GetObjectBy (pobjMember);
														
						if (objRefObject.IsArray)
						{
							stbExtraCollectionTable = new StringBuilder ();
							
							objExtRefObject = this.ObjectSetManager [objRefObject.SetName] [objRefObject.Type];
																
							this.AddStartTableForRefArray (ref stbExtraCollectionTable, pobjObject, pobjMember, objRefObject);
							
							stbExtraCollectionTable.Append (TextHelper.NL_1_TAB);
							
							if (objRefObject.IDType != null)
							{											
								this.AddPrimaryKeyColumn (ref stbExtraCollectionTable, objRefObject.IDType.Value, objRefObject.IDMaxLength);
								
								this.AddForeignConstraint (ref stbExtraFKs, pobjObject, pobjMember, objExtRefObject);
								
								stbExtraCollectionTable.Append (",");
								stbExtraCollectionTable.Append (TextHelper.NL_1_TAB);
							}
							
							this.AddForeignColumn (ref stbExtraCollectionTable, pobjObject);
							
							stbExtraCollectionTable.Append (",");
							stbExtraCollectionTable.Append (TextHelper.NL_1_TAB);
							
							this.AddForeignColumn (ref stbExtraCollectionTable, objExtRefObject);
							
							stbExtraCollectionTable.Append (",");
							stbExtraCollectionTable.Append (TextHelper.NL_1_TAB);
							
							this.AddForeignConstraint (ref stbExtraCollectionTable, objRefObject, pobjObject);
							
							stbExtraCollectionTable.Append (",");
							stbExtraCollectionTable.Append (TextHelper.NL_1_TAB);
							this.AddForeignConstraint (ref stbExtraCollectionTable, objRefObject, pobjMember, objExtRefObject);
							
							this.AddEndTable (ref stbExtraCollectionTable);
												
							this.AddAlterUniqueConstraint (ref stbExtraCollectionTable, pobjObject, pobjMember, objRefObject, objExtRefObject);
							stbExtraCollectionTable.AppendLine ();
							
							dicExtraFKTables.Add (objRefObject, stbExtraCollectionTable);
						}
					}
						
					#endregion
				
					
					this.AddStartTable (ref stbSql, pobjObject);
				
					if (pobjObject.IDType != null)
					{
						stbSql.Append (TextHelper.NL_1_TAB);
						this.AddPrimaryKeyColumn (ref stbSql, pobjObject.IDType.Value, pobjObject.IDMaxLength);
					}
					
					this.AddColumn (ref stbSql, pobjObject, pobjObject.Members [0]);
					
					for (intI = 1; intI < pobjObject.Members.Count; intI ++)
					{
						this.AddColumn (ref stbSql, pobjObject, pobjObject.Members [intI]);
					}
					
					if (pobjObject.IDType != null)
					{
						stbSql.Append (",");
						stbSql.Append (TextHelper.NL_1_TAB);
						this.AddPrimaryKeyConstraint (ref stbSql);
					}							
					
					stbSql.Append (stbExtraFKs.ToString ());
					stbExtraFKs.Length = 0;
					
					this.AddEndTable (ref stbSql);
					
					#region Adicion de los Constraints
					
					pobjObject.UniqueRules.ForEach (
						delegate (UniqueRule pobjUniqueRule)
						{
							stbSql.AppendLine ();
							this.AddAlterUniqueConstraint (ref stbSql, pobjObject, pobjUniqueRule);
						}
					);
					
					pobjObject.OnlyOneRules.ForEach (
						delegate (OnlyOneRule pobjOnlyOneRule)
						{
							stbSql.AppendLine ();
							this.AddOnlyOneConstraint (ref stbSql, pobjOnlyOneRule);
						}
					);
					
					#endregion
				}
				
				if (objArrayNotUsed != null && dicExtraFKTables.ContainsKey (objArrayNotUsed))
				{
					stbSql.Append (dicExtraFKTables [objArrayNotUsed].ToString ());
					dicExtraFKTables.Remove (objArrayNotUsed);
					objArrayNotUsed = null;
				}
				
				this._Writer.Write (stbSql);					
				this._Writer.Flush ();
				
				stbSql.Length = 0;
			}
		}
		
		
		private void AddColumn (ref StringBuilder pstbSql, XmlObject pobjObject, XmlMember pobjMember)
		{
			XmlObject objRefObject;
			XmlObject objExtRefObject;
			
				
			if (pobjMember.Type == ValueTypes.Custom)
			{
				objRefObject = this.ObjectSetManager.GetObjectBy (pobjMember);
				
				if (!objRefObject.IsArray) 
				{
					objExtRefObject = this.ObjectSetManager [objRefObject.SetName] [objRefObject.Type];
					
					pstbSql.Append (",");
					pstbSql.Append (TextHelper.NL_1_TAB);
					this.AddForeignColumn (ref pstbSql, pobjMember, objExtRefObject);
					
					pstbSql.Append (",");
					pstbSql.Append (TextHelper.NL_1_TAB);
					
					this.AddForeignConstraint (ref pstbSql, pobjObject, pobjMember, objExtRefObject);
				}
			}
			else
			{
				pstbSql.Append (",");
				pstbSql.Append (TextHelper.NL_1_TAB);
				this.AddColumn (ref pstbSql, pobjMember);
			}
		}		
		
		
		protected void AddAlterUniqueConstraint (ref StringBuilder pstbSql, XmlObject pobjObject, UniqueRule pobjUniqueRule)
		{
			List<string> strColumnNames;
			
			
			strColumnNames = new List<string> (pobjUniqueRule.Members.Count);
			
			foreach (XmlMember objMember in pobjUniqueRule.Members)
			{
				if (!string.IsNullOrEmpty (objMember.CustomType))
				{
					strColumnNames.Add ("ID" + objMember.CustomType);
				}
				else
				{
					strColumnNames.Add (objMember.Name);
				}
			}
			
			this.AddAlterUniqueConstraint (ref pstbSql, pobjObject, pobjUniqueRule.Name, strColumnNames.ToArray ());
		}
				
		#endregion
		
		
		#region Funciones de Parametros
		
		protected string GenerateSPParameterName (string pstrBasicParameterName)
		{
			return this.ParamsSeparator + "p" + pstrBasicParameterName;
		}
		
		
		protected string GenerateSPParameterName (XmlMember pobjMember)
		{
			return this.GenerateSPParameterName (pobjMember, true);
		}
		
		
		protected string GenerateSPParameterName (XmlMember pobjMember, bool pblnIncludesP)
		{
			XmlObject objRefObject;
			
			string strResult;
			
		
			if (pobjMember.Type == ValueTypes.Custom)
			{
				objRefObject = this._ObjectSetManager.GetObjectBy (pobjMember);
				
				if (objRefObject.IsArray)
				{
					return string.Empty;
					//throw new NotSupportedException ("No se pueden generar un parametro de arreglo");
				}
				
				strResult = "ID" + pobjMember.Name;
			}
			else
			{
				strResult = pobjMember.Name;
			}
			
			return (pblnIncludesP ? "p" : string.Empty) + strResult;
		}
		
		#endregion
		
		
		#region Funciones para Sobreescribir
		
		#region Generacion de Estructura
		
		protected abstract void AddColumn (ref StringBuilder pstbSql, XmlMember pobjMember);		
		
		protected abstract void AddDeleteDataBase ();		
		
		protected abstract void AddCreateDataBase ();
		
		protected abstract string GenerateTableName (XmlObject pobjObject);
		protected abstract string GenerateTableNameForRefArray (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjRefObject);
		
		protected abstract void AddDeleteTable (XmlObject pobjObject);
		protected abstract void AddDeleteTable (string pstrTableName);	
		
		protected abstract void AddStartTable (ref StringBuilder pstbSql, XmlObject pobjObject);
		protected abstract void AddStartTableForRefArray (ref StringBuilder pstbSql, XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjRefObject);
		
		protected abstract void AddEndTable (ref StringBuilder pstbSql);
		
		protected abstract void AddPrimaryKeyColumn (ref StringBuilder pstbSql, IDTypes pobjIDType, byte? pbytIDMaxLength);

		protected abstract void AddForeignColumn (ref StringBuilder pstbSql, XmlMember pobjMember, XmlObject pobjExtRefObject);
		protected abstract void AddForeignColumn (ref StringBuilder pstbSql, XmlObject pobjObject);
		
		protected abstract string GenerateTraslateDbType (ValueTypes pobjValueType, ushort? pshrApplicableMaxLength, byte? pbytNumericPrecision, byte? pbytNumericScale); 
		protected abstract string GenerateTraslateDbType (XmlMember pobjMember);

		protected abstract string GenerateTraslateDbIDType (IDTypes? pobjIDType, byte? pbytIDMaxLength);

		#endregion

		#region Procedimientos Almacenados

		protected abstract void AddDeleteSP (string pstrSPName);

		//protected abstract void AddCreateSPCount (XmlObject pobjObject);
		protected abstract void AddCreateSPInsertUpdate (XmlObject pobjObject);
		protected abstract void AddCreateSPDeleteByID (XmlObject pobjObject);
		//protected abstract void AddCreateSPSelectByID (XmlObject pobjObject);
		protected abstract void AddCreateSPSelectList (XmlObject pobjObject);
		protected abstract void AddCreateSPSelectEnabledList (XmlObject pobjObject);
		protected abstract void AddCreateSPSelect (XmlObject pobjObject);
		
		protected abstract void AddCreateSPInsertArray (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject); 
		protected abstract void AddCreateSPDeleteArrayByRef (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjRefObject);
		protected abstract void AddCreateSPDeleteArrayBySelf (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjRefObject);
		protected abstract void AddCreateSPSelectRefArrayByMe (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject); 
		protected abstract void AddCreateSPSelectArraySelfByRef (XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjExtRefObject); 

		protected abstract void AddStartSP (XmlObject pobjObject, string pstrOperationName);
		protected abstract void AddEndSP ();
		
		#endregion

		#region Constraints

		protected abstract void AddPrimaryKeyConstraint (ref StringBuilder pstbSql);
		
		protected abstract void AddOnlyOneConstraint (ref StringBuilder pstbSql, OnlyOneRule pobjOnlyOneRule);
		
		protected abstract void AddForeignConstraint (ref StringBuilder pstbSql, XmlObject pobjRefObject, XmlObject pobjObject);
		
		protected abstract void AddForeignConstraint (ref StringBuilder pstbSql, 
			XmlObject pobjRefObject, XmlMember pobjMember, XmlObject pobjExtRefObject);
			
		protected abstract void AddAlterUniqueConstraint (ref StringBuilder pstbSql, XmlObject pobjObject, string pstrUniqueName, string [] pstrColumnNames);
		protected abstract void AddAlterUniqueConstraint (ref StringBuilder pstbSql, XmlObject pobjObject, XmlMember pobjMember, XmlObject pobjRefObject, XmlObject pobjExtRefObject);
		
		#endregion
				
		#region Parametros
		
		protected abstract string GenerateSPParameter (string pstrBasicParameterName, ValueTypes pobjValueType, ushort? pshrMaxLength, ParameterDirection penuParameterDirection, string pstrDefaultValue);
		protected abstract string GenerateSPParameter (string pstrBasicParameterName, ValueTypes pobjValueType, ushort? pshrMaxLength, ParameterDirection penuParameterDirection);
		protected abstract string GenerateSPParameter (string pstrBasicParameterName, ValueTypes pobjValueType, ushort? pshrMaxLength);
		protected abstract string GenerateSPParameter (string pstrBasicParameterName, ValueTypes pobjValueType);
		
		protected abstract string GenerateSPParameter (XmlMember pobjMember, ParameterDirection penuParameterDirection, string pstrDefaultValue);
		protected abstract string GenerateSPParameter (XmlMember pobjMember, ParameterDirection penuParameterDirection);
		protected abstract string GenerateSPParameter (XmlMember pobjMember);
		protected abstract string GenerateSPParameterUseFilter (XmlMember pobjMember);
		
		protected abstract string GenerateSPParameterID (IDTypes? pobjIDType, byte? pbytIDMaxLength);
		protected abstract string GenerateSPParameterID (IDTypes? pobjIDType, byte? pbytIDMaxLength, ParameterDirection penuParameterDirection);
		protected abstract string GenerateSPParameterID (string pstrBasicParameterName, IDTypes? pobjIDType, byte? pbytIDMaxLength);
		protected abstract string GenerateSPParameterID (string pstrBasicParameterName, IDTypes? pobjIDType, byte? pbytIDMaxLength, ParameterDirection penuParameterDirection);
		
		protected abstract string GenerateStartSPParameter (string pstrBasicParameterName, ParameterDirection penuParameterDirection);
		
		#endregion
		
		#endregion
	}
}